{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2bb502b0",
   "metadata": {},
   "source": [
    "-- 创建部门表\n",
    "CREATE TABLE departments (\n",
    "    dept_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',\n",
    "    dept_name VARCHAR(50) NOT NULL COMMENT '部门名称',\n",
    "    location VARCHAR(100) COMMENT '部门位置',\n",
    "    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'\n",
    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门信息表';\n",
    "\n",
    "-- 创建员工表\n",
    "CREATE TABLE employees (\n",
    "    emp_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID',\n",
    "    emp_name VARCHAR(50) NOT NULL COMMENT '员工姓名',\n",
    "    position VARCHAR(50) COMMENT '职位',\n",
    "    hire_date DATE NOT NULL COMMENT '入职日期',\n",
    "    salary DECIMAL(10,2) NOT NULL COMMENT '薪资',\n",
    "    email VARCHAR(100) UNIQUE COMMENT '邮箱',\n",
    "    phone VARCHAR(20) COMMENT '电话',\n",
    "    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'\n",
    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工信息表';\n",
    "\n",
    "-- 创建员工部门关系表（多对多关联）\n",
    "CREATE TABLE emp_dept_relation (\n",
    "    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '关系ID',\n",
    "    emp_id INT NOT NULL COMMENT '员工ID',\n",
    "    dept_id INT NOT NULL COMMENT '部门ID',\n",
    "    join_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '加入部门日期', -- 兼容MySQL 5.7+的时间默认值写法\n",
    "    FOREIGN KEY (emp_id) REFERENCES employees(emp_id) ON DELETE CASCADE,\n",
    "    FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE CASCADE,\n",
    "    UNIQUE KEY uk_emp_dept (emp_id, dept_id) COMMENT '员工与部门组合唯一'\n",
    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工部门关联表';\n",
    "\n",
    "-- 插入测试数据：部门表\n",
    "INSERT INTO departments (dept_name, location) VALUES\n",
    "('技术部', '北京'),\n",
    "('市场部', '上海'),\n",
    "('人力资源部', '广州'),\n",
    "('财务部', '深圳');\n",
    "\n",
    "-- 插入测试数据：员工表\n",
    "INSERT INTO employees (emp_name, position, hire_date, salary, email, phone) VALUES\n",
    "('张三', '高级工程师', '2020-01-15', 25000.00, 'zhang.san@example.com', '13800138001'),\n",
    "('李四', '产品经理', '2021-03-22', 22000.00, 'li.si@example.com', '13900139002'),\n",
    "('王五', '市场专员', '2022-05-30', 15000.00, 'wang.wu@example.com', '13700137003'),\n",
    "('赵六', '财务主管', '2019-09-05', 18000.00, 'zhao.liu@example.com', '13600136004'),\n",
    "('孙七', '人事专员', '2023-02-18', 12000.00, 'sun.qi@example.com', '13500135005'),\n",
    "('周八', '前端开发', '2021-07-10', 20000.00, 'zhou.ba@example.com', '13400134006'),\n",
    "('吴九', '后端开发', '2020-11-25', 23000.00, 'wu.jiu@example.com', '13300133007');\n",
    "\n",
    "-- 插入测试数据：员工部门关系表（支持一人多部门）\n",
    "INSERT INTO emp_dept_relation (emp_id, dept_id, join_date) VALUES\n",
    "(1, 1, '2020-01-15 00:00:00'),  -- 张三属于技术部\n",
    "(2, 1, '2021-03-22 00:00:00'),  -- 李四属于技术部\n",
    "(3, 2, '2022-05-30 00:00:00'),  -- 王五属于市场部\n",
    "(4, 4, '2019-09-05 00:00:00'),  -- 赵六属于财务部\n",
    "(5, 3, '2023-02-18 00:00:00'),  -- 孙七属于人力资源部\n",
    "(6, 1, '2021-07-10 00:00:00'),  -- 周八属于技术部\n",
    "(7, 1, '2020-11-25 00:00:00'),  -- 吴九属于技术部\n",
    "(2, 2, '2022-01-01 00:00:00');   -- 李四同时兼任市场部产品工作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8b9c2018",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple\n",
      "Collecting pymysql\n",
      "  Downloading https://pypi.tuna.tsinghua.edu.cn/packages/7c/4c/ad33b92b9864cbde84f259d5df035a6447f91891f5be77788e2a3892bce3/pymysql-1.1.2-py3-none-any.whl (45 kB)\n",
      "Installing collected packages: pymysql\n",
      "Successfully installed pymysql-1.1.2\n"
     ]
    }
   ],
   "source": [
    "! pip install pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3e92f86d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 连接数据库\n",
    "import os\n",
    "from dotenv import load_dotenv\n",
    "from langchain_community.utilities import SQLDatabase\n",
    "\n",
    "load_dotenv()\n",
    "\n",
    "db_host = os.getenv(\"DB_HOST\")\n",
    "db_port = os.getenv(\"DB_PORT\")\n",
    "db_user = os.getenv(\"DB_USER\")\n",
    "db_password = os.getenv(\"DB_PASSWORD\")\n",
    "db_name = os.getenv(\"DB_NAME\")\n",
    "\n",
    "MYSQL_URI = \"mysql+pymysql://{}:{}@{}:{}/{}\".format(\n",
    "    db_user, db_password, db_host, db_port, db_name\n",
    ")  \n",
    "\n",
    "db = SQLDatabase.from_uri(MYSQL_URI)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "de218d16",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain_deepseek import ChatDeepSeek\n",
    "\n",
    "llm = ChatDeepSeek(model=\"deepseek-chat\", temperature=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "c346d151",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT COUNT(*) AS `total_employees` FROM `employees`\n"
     ]
    }
   ],
   "source": [
    "from langchain.chains import create_sql_query_chain\n",
    "from langchain_core.output_parsers import StrOutputParser\n",
    "from langchain_core.runnables import RunnablePassthrough\n",
    "from langchain.prompts import PromptTemplate\n",
    "\n",
    "base_chain = create_sql_query_chain(llm, db)\n",
    "\n",
    "format_chain = (RunnablePassthrough()\n",
    "                | PromptTemplate.from_template(\n",
    "                    \"\"\"\n",
    "                    提取出文本中的sql语句，并返回\n",
    "                    只需要返回sql语句，不需要其他任何内容，不能有```sql\n",
    "                    你的回答必须是sql语句\n",
    "\n",
    "                    {query}\n",
    "                    \"\"\"\n",
    "                )\n",
    "                | llm \n",
    "                | StrOutputParser())\n",
    "\n",
    "query_chain = base_chain | format_chain\n",
    "\n",
    "resp = query_chain.invoke({\"question\": \"员工表中有多少人？\"})\n",
    "\n",
    "print(resp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "de9e050b",
   "metadata": {},
   "outputs": [],
   "source": [
    "answer_prompt = PromptTemplate.from_template(\n",
    "    \"\"\"给定以下用户问题，sql语句和执行sql语句的结果，回答用户问题。\n",
    "    用户问题：{question}\n",
    "    sql语句：{query}\n",
    "    执行sql语句的结果：{result}\n",
    "    你的回答：\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "5e33cdde",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建执行sql语句的工具\n",
    "from langchain_community.tools import QuerySQLDataBaseTool\n",
    "\n",
    "execute_sql_tool = QuerySQLDataBaseTool(db=db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "c285e835",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1.生成sql语句 2.执行sql语句 \n",
    "\n",
    "from operator import itemgetter\n",
    "\n",
    "chain = (RunnablePassthrough.assign(query= query_chain).assign(result= itemgetter(\"query\")|execute_sql_tool)\n",
    "         |answer_prompt\n",
    "         |llm\n",
    "         |StrOutputParser()\n",
    "         )\n",
    "\n",
    "rep = chain.invoke({\"question\": \"请查询所有员工信息\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "f6598257",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "根据查询结果，以下是所有员工的信息：\n",
      "\n",
      "| 员工ID | 姓名 | 职位         | 入职日期     | 月薪       | 邮箱                     | 电话        | 创建时间               |\n",
      "|--------|------|--------------|--------------|------------|--------------------------|-------------|------------------------|\n",
      "| 1      | 张三 | 高级工程师   | 2020-01-15   | 25000.00   | zhang.san@example.com    | 13800138001 | 2025-09-17 16:52:49    |\n",
      "| 2      | 李四 | 产品经理     | 2021-03-22   | 22000.00   | li.si@example.com        | 13900139002 | 2025-09-17 16:52:49    |\n",
      "| 3      | 王五 | 市场专员     | 2022-05-30   | 15000.00   | wang.wu@example.com      | 13700137003 | 2025-09-17 16:52:49    |\n",
      "| 4      | 赵六 | 财务主管     | 2019-09-05   | 18000.00   | zhao.liu@example.com     | 13600136004 | 2025-09-17 16:52:49    |\n",
      "| 5      | 孙七 | 人事专员     | 2023-02-18   | 12000.00   | sun.qi@example.com       | 13500135005 | 2025-09-17 16:52:49    |\n",
      "| 6      | 周八 | 前端开发     | 2021-07-10   | 20000.00   | zhou.ba@example.com      | 13400134006 | 2025-09-17 16:52:49    |\n",
      "| 7      | 吴九 | 后端开发     | 2020-11-25   | 23000.00   | wu.jiu@example.com       | 13300133007 | 2025-09-17 16:52:49    |\n",
      "\n",
      "共查询到7名员工的信息，包含员工ID、姓名、职位、入职日期、月薪、邮箱、电话和创建时间等详细信息。\n"
     ]
    }
   ],
   "source": [
    "print(rep)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "d72d8761",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "根据您提供的SQL执行结果，出现了语法错误(1064)，这通常是因为SQL语句的格式问题导致的。错误信息显示SQL语法在靠近```sql标记时出现问题，这表明可能是执行环境（如Python中的pymysql）无法正确处理包含代码标记的SQL语句。\n",
      "\n",
      "**正确的SQL语句应该是：**\n",
      "```sql\n",
      "SELECT d.`dept_name`, e.`emp_name`, e.`salary`\n",
      "FROM `departments` d\n",
      "INNER JOIN `emp_dept_relation` edr ON d.`dept_id` = edr.`dept_id`\n",
      "INNER JOIN `employees` e ON edr.`emp_id` = e.`emp_id`\n",
      "INNER JOIN (\n",
      "    SELECT d2.`dept_id`, MAX(e2.`salary`) as max_salary\n",
      "    FROM `departments` d2\n",
      "    INNER JOIN `emp_dept_relation` edr2 ON d2.`dept_id` = edr2.`dept_id`\n",
      "    INNER JOIN `employees` e2 ON edr2.`emp_id` = e2.`emp_id`\n",
      "    GROUP BY d2.`dept_id`\n",
      ") dept_max ON d.`dept_id` = dept_max.`dept_id` AND e.`salary` = dept_max.max_salary\n",
      "ORDER BY d.`dept_name`;\n",
      "```\n",
      "\n",
      "**这个SQL语句的逻辑是：**\n",
      "1. 首先通过子查询找出每个部门的最高工资\n",
      "2. 然后将这个结果与员工表、部门表进行关联\n",
      "3. 最终返回每个部门中工资最高的员工信息\n",
      "\n",
      "**建议：**\n",
      "请直接执行去掉代码标记(```sql)的纯SQL语句，这样应该能够成功运行并返回各个部门中工资最高的员工信息，包括部门名称、员工姓名和对应的工资数额。\n"
     ]
    }
   ],
   "source": [
    "rep2 = chain.invoke({\"question\": \"各个部门中员工工资最高的分别是谁？\"})\n",
    "print(rep2)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "langchain_learn",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
